﻿using Microsoft.Extensions.Caching.Distributed;
using MySqlConnector;
using Newtonsoft.Json.Linq;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;

namespace BasicModuleCache
{
    public class MysqlCache : IDistributedCache
    {
        public byte[]? Get(string key)
        {
            //using (var connection = new MySqlConnection("Server=localhost;Port=3306;Database=YDT-Ebusiness;Uid=root;Pwd=123456;"))
            //{
            //    var command = connection.CreateCommand();
            //    command.CommandText = "select * from caches where CacheKey=@CacheKey";
            //    command.Parameters.AddWithValue("@CacheKey", key);

            //    connection.Open();
            //    var Reader = command.ExecuteReader();
            //    // string name = Reader.GetSchemaTable();
            //    while (Reader.Read()){
            //        Console.WriteLine($"ID: {Reader[""]}, Name: {Reader[""]}, Age: {Reader[""]}");
            //    }
            //    return Encoding.UTF8.GetBytes("数据");
            //}
            SqlSugarClient Db = new SqlSugarClient(new ConnectionConfig()
            {
                //Server=localhost;Port=3306;Database=YDT-Ebusiness;Uid=root;Pwd=123456; mysql
                //Data Source=zdf;Initial Catalog=Ydt_Test;Integrated Security=True;Trust Server Certificate=True  sqlserver
                ConnectionString = "Server=localhost;Port=3306;Database=YDT-Ebusiness;Uid=root;Pwd=123456;",
                DbType = DbType.MySql,
                IsAutoCloseConnection = true
            },
           db =>
           {
               db.Aop.OnLogExecuting = (sql, pars) =>
               {
                   //获取原生SQL推荐 5.1.4.63  性能OK
                   //Console.WriteLine(UtilMethods.GetNativeSql(sql, pars));
                   //获取无参数化SQL 对性能有影响，特别大的SQL参数多的，调试使用
                   //Console.WriteLine(UtilMethods.GetSqlString(DbType.SqlServer,sql,pars))

               };
           });

            Db.CodeFirst.InitTables(typeof(MysqlCacheModel));//这样一个表就能成功创建了
            return Encoding.UTF8.GetBytes(Db.Queryable<MysqlCacheModel>().Where(x => x.CacheKey == key).First().CacheValue);
        }

        public async Task<byte[]?> GetAsync(string key, CancellationToken token = default)
        {
            // 异步操作通常用于访问外部资源，但在这个内存缓存示例中，我们可以直接返回结果
            return Get(key);
        }

        public void Refresh(string key)
        {
            // 在内存缓存中，刷新操作可能不需要执行任何操作
        }

        public async Task RefreshAsync(string key, CancellationToken token = default)
        {
            // 异步操作通常用于访问外部资源，但在这个内存缓存示例中，我们可以直接返回
            Refresh(key);
        }

        public void Remove(string key)
        {
            //Caches.TryRemove(key, out _);
        }

        public async Task RemoveAsync(string key, CancellationToken token = default)
        {
            Remove(key);
        }

        public void Set(string key, byte[] value, DistributedCacheEntryOptions options)
        {
            //// 将缓存写入MySQL数据库
            //using (var connection = new MySqlConnection("Server=localhost;Port=3306;Database=YDT-Ebusiness;Uid=root;Pwd=123456;"))
            //{
            //    var command = connection.CreateCommand();
            //    command.CommandText = "INSERT INTO caches (CacheKey,CacheValue) VALUES (@CacheKey,@CacheValue)";
            //    command.Parameters.AddWithValue("@CacheKey", key);
            //    command.Parameters.AddWithValue("@CacheValue", Encoding.UTF8.GetString(value));

            //    connection.Open();
            //    command.ExecuteNonQuery();
            //}

            SqlSugarClient Db = new SqlSugarClient(new ConnectionConfig()
            {
                //Server=localhost;Port=3306;Database=YDT-Ebusiness;Uid=root;Pwd=123456; mysql
                //Data Source=zdf;Initial Catalog=Ydt_Test;Integrated Security=True;Trust Server Certificate=True  sqlserver
                ConnectionString = "Server=localhost;Port=3306;Database=YDT-Ebusiness;Uid=root;Pwd=123456;",
                DbType = DbType.MySql,
                IsAutoCloseConnection = true
            },
            db =>
            {
                db.Aop.OnLogExecuting = (sql, pars) =>
                {
                    //获取原生SQL推荐 5.1.4.63  性能OK
                    //Console.WriteLine(UtilMethods.GetNativeSql(sql, pars));
                    //获取无参数化SQL 对性能有影响，特别大的SQL参数多的，调试使用
                    //Console.WriteLine(UtilMethods.GetSqlString(DbType.SqlServer,sql,pars))

                };
            });

            Db.CodeFirst.InitTables(typeof(MysqlCacheModel));//这样一个表就能成功创建了

            MysqlCacheModel loggerModel = new MysqlCacheModel() { CacheKey = key, CacheValue = Encoding.UTF8.GetString(value) };
            int i = Db.Insertable<MysqlCacheModel>(loggerModel).ExecuteCommand();
        }

        public async Task SetAsync(string key, byte[] value, DistributedCacheEntryOptions options, CancellationToken token = default)
        {
            Set(key, value, options);
        }
    }
}
